//
//  CLSQLSQL.h
//  CLBrowser
//
//  Created by 梁慧聪 on 2017/4/7.
//  Copyright © 2021年 梁慧聪. All rights reserved.
//

#ifndef CLSQLSQL_h
#define CLSQLSQL_h

#pragma mark - History

#define CL_TABLE_HISTORY              @"history"
#define CL_TABLE_HISTORY_HOUR_MINUTE_INDEX @"history_hour_minute_index"

#define CL_FIELD_URL                  @"url"
#define CL_FIELD_TITLE                @"title"
#define CL_FIELD_HOUR_MINUTE          @"hour_minute"
#define CL_FIELD_TIME                 @"time"

#define CL_SQL_CREATE_HISTORY_TABLE \
    @"CREATE TABLE IF NOT EXISTS " CL_TABLE_HISTORY @" ("    \
        CL_FIELD_URL             @" TEXT NOT NULL, "         \
        CL_FIELD_TITLE           @" TEXT, "                  \
        CL_FIELD_HOUR_MINUTE     @" TEXT, "                  \
        CL_FIELD_TIME            @" TEXT NOT NULL, "         \
        @"PRIMARY KEY(" CL_FIELD_URL @"," CL_FIELD_TIME @")" \
    @");"

#define CL_SQL_CREATE_HISTORY_INDEX_TABLE               \
    @"CREATE INDEX IF NOT EXISTS "                      \
        CL_TABLE_HISTORY_HOUR_MINUTE_INDEX @" ON "      \
        CL_TABLE_HISTORY @"(" CL_FIELD_HOUR_MINUTE @", " CL_FIELD_TIME @");"

#define CL_SQL_INSERT_OR_IGNORE_HISTORY                 \
    @"INSERT OR IGNORE INTO " CL_TABLE_HISTORY  @" ("   \
        CL_FIELD_URL             @", "                  \
        CL_FIELD_TITLE           @", "                  \
        CL_FIELD_HOUR_MINUTE     @", "                  \
        CL_FIELD_TIME                                   \
    @") VALUES(?, ?, ?, ?);"

// It's not particularly efficient, but works if iterating over a
// small index, see https://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor for details,
// I just don't want to optimize, sigh.
#define CL_SQL_SELECT_HISTORY                \
    @"SELECT * FROM " CL_TABLE_HISTORY @" ORDER BY "  \
        CL_FIELD_TIME           @" DESC,"         \
        CL_FIELD_HOUR_MINUTE    @" DESC "         \
    @"LIMIT ? OFFSET ?;"

#define CL_SQL_SELECT_TODAY_YESTERDAY_HISTORY     \
    @"SELECT * FROM " CL_TABLE_HISTORY @" "       \
        @"WHERE "     CL_FIELD_TIME @" = ? "      \
        @"ORDER BY "  CL_FIELD_HOUR_MINUTE @" "   \
    @"DESC;"

#define CL_SQL_DELETE_HISTORY_RECORD     \
    @"DELETE FROM " CL_TABLE_HISTORY @" "       \
        @"WHERE "   CL_FIELD_URL @" = ? "       \
        @"AND "     CL_FIELD_TIME @" = ?;"      \

#define CL_SQL_DELETE_ALL_HISTORY_RECORD     \
    @"DELETE FROM " CL_TABLE_HISTORY @";"

#endif /* CLSQLSQL_h */
